package DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Model.Laboratorio;
import Utils.Conexao;
import com.mysql.jdbc.CallableStatement;


public class LaboratorioDAO {
	public static void Insert(Laboratorio lab) throws SQLException
	{
		Connection con = Conexao.getConection();		
		String mysqlCall = "call spc_insert_laboratorio(?, ?, ?, ?, ?)";		
		CallableStatement call = LaboratorioDAO.setObjectCallableStatement(lab, mysqlCall, con);
		call.execute();
		
		call.close();		
		con.close();		
	}
	
	
	public static void Update(Laboratorio lab) throws SQLException
	{		
		Connection con = Conexao.getConection();		
		String mysqlCall = "call spc_update_laboratorio(?, ?, ?, ?, ?, ?)";		
		CallableStatement call = LaboratorioDAO.setObjectCallableStatement(lab, mysqlCall, con) ;
		call.execute();
		
		call.close();
		con.close();
	}	
	
	public static void Delete(int idLaboratorio) throws SQLException
	{
		Connection con = Conexao.getConection();
		String mysqlCall = "call spc_delete_laboratorio(?)";
		CallableStatement call =(CallableStatement) con.prepareCall(mysqlCall);
		call.setInt(1, idLaboratorio);
		call.execute();
		
		call.close();
		con.close();
	}
	
	public static Laboratorio LoadObject(int idLaboratorio) throws SQLException
	{		
		Laboratorio laboratorio = null;
		Connection con = Conexao.getConection();
		String mysqlCall = "call spc_select_laboratorio_por_id(?) ";
		
		CallableStatement call = (CallableStatement) con.prepareCall(mysqlCall);
		call.setInt(1, idLaboratorio);
		ResultSet res = call.executeQuery();
		
		if(res.next())
			laboratorio = LaboratorioDAO.setInstance(res);		
		
		call.close();
		con.close();
		
		return laboratorio;
	}
	
	public static ArrayList<Laboratorio> GetCidArrayListLaboratorios()
	{
		try
		{
			ArrayList<Laboratorio> arrLaboratorio = new ArrayList<Laboratorio>();
			Connection con = Conexao.getConection();
			String mysqlCall = "call spc_select_laboratorios()";
			CallableStatement call  =(CallableStatement) con.prepareCall(mysqlCall);
			ResultSet res = call.executeQuery();
			
			while(res.next())
			{
				Laboratorio lab = LaboratorioDAO.setInstance(res); 
				arrLaboratorio.add(lab);
			}
			
			call.close();
			con.close();			
			return arrLaboratorio;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}		
	}
	
	private static CallableStatement setObjectCallableStatement(Laboratorio lab, String mysqlCall, Connection con ) throws SQLException
	{				
		CallableStatement call = (CallableStatement) con.prepareCall(mysqlCall);
		call.setString(1, lab.getNomeLaboratorio());
		call.setString(2,lab.getEndereco());
		call.setString(3, lab.getTelefone());
		call.setString(4, lab.getCep());
		call.setString(5, lab.getEmail());
		
		if(lab.getId() > 0)
			call.setInt(6, lab.getId());
	
		return call; 		
	}
	
	public static Laboratorio setInstance(ResultSet res) throws SQLException
	{
		Laboratorio lab = new Laboratorio();
		lab.setId(res.getInt("id_laboratorio"));
		lab.setNomeLaboratorio(res.getString("nome_laboratorio"));
		lab.setCep(res.getString("cep"));
		lab.setEndereco(res.getString("endereco"));
		lab.setTelefone(res.getString("telefone"));
		lab.setCep(res.getString("cep"));
		lab.setEmail(res.getString("email"));
		
		return lab;
	}
	
}
